package com.huatai.vba.dal.jdbc;

import java.sql.*;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

public class DBUtils {

	private static final Logger logger = LoggerFactory.getLogger(DBUtils.class);

	private DBUtils() {
	}

	static {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (Exception e) {
			e.printStackTrace();

		}
	}

	public static PreparedStatement getPs(Connection conn, String sql) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ps;
	}

	public static ResultSet getRs(PreparedStatement ps) {
		ResultSet rs = null;
		try {
			rs = ps.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}

	public static void close(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void close(PreparedStatement ps) {
		try {
			if (ps != null) {
				ps.close();
				ps = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void close(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static List<Map<String, Object>> executeQuery(String url, String user, String password, String sql)
			throws Exception {
		logger.info("查询sql:" + sql);
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Map<String, Object>> list = null;
		try {
			list = Lists.newArrayList();
			conn = DriverManager.getConnection(url, user, password);
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> map = Maps.newHashMap();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					String columnName = rsmd.getColumnName(i);
					map.put(columnName, rs.getObject(columnName));
				}
				list.add(map);
			}
			if (list.toString().length() > 20) {
				logger.info("查询结果:" + list.toString().substring(0, 20) + "......");
			} else {
				logger.info("查询结果:" + list);
			}

		} catch (Exception e) {
			throw e;
		} finally {
			DBUtils.close(rs);
			DBUtils.close(pstm);
			DBUtils.close(conn);
		}
		return list;

	}

}
